package dbaccessors;

import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;

import dataobjects.*;


/**
 * Only this class can access to DB server, thus all the methods must be
 * implemented in synchronized way. It is a singleton.
 * 
 * 
 */

public class DBAccessor {
	private final String USERNAME = "manager";
	private final String PASSWORD = "1234";
	private final String URL = "jdbc:mysql://localhost:3306/";
	private final String DBNAME = "Collectomania1.0";
	private Connection conn;
	private static DBAccessor instance = null;

	private DBAccessor() {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(this.URL + this.DBNAME,
					this.USERNAME, this.PASSWORD);
		} catch (Exception e) {
			System.out.println("class not found");
			e.printStackTrace();
		}
	}

	public static DBAccessor getInstance() {
		if (instance == null) {
			instance = new DBAccessor();
		}
		return instance;
	}

	// methods
	public boolean register(String username, String name, String email,
			String password) {
		if (!username.contains("@"))
			return false;
		int updateQuery1 = 0, updateQuery2 = 0;
		try {
			PreparedStatement statement = this.conn.prepareStatement("INSERT INTO `collectomania1.0`.`tbluser` "+
																		"VALUES (?,?,?,0,NULL)");
			statement.setString(1, username);
			statement.setString(2, name);
			statement.setString(3, email);
			updateQuery1 = statement.executeUpdate();
			
			statement = this.conn.prepareStatement("INSERT INTO `collectomania1.0`.`tblpassword` "+
													"VALUES (?,?)");
			statement.setString(1, username);
			statement.setString(2, password);
			updateQuery2 = statement.executeUpdate();
			if (updateQuery1 != 0 && updateQuery2 != 0)
				return true;
			else
				return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public boolean login(String username, String password) {
		if (!isRegistered(username))
			return false;
		String query = "Select * FROM `collectomania1.0`.`tblpassword` WHERE `username`= ?";
		PreparedStatement statement;
		try {
			statement = this.conn.prepareStatement(query);
			statement.setString(1, username);
			ResultSet rs = statement.executeQuery();
			if (rs.next()) {
				String dbtime = rs.getString(2);
				if (dbtime.equals(password)) {
					query = "UPDATE `collectomania1.0`.`tblusers` SET `isLogged`=1 WHERE `username`= ?";
					statement = this.conn.prepareStatement(query);
					statement.setString(1, username);
					if (statement.executeUpdate() != 0)
						return true;
					return false;
				}
				return false;
			}
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	private boolean isRegistered(String username) {
		String query = "Select * FROM `collectomania1.0`.`tblusers` WHERE `username`="
				+ "'" + username + "'";
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			if (rs.next())
				return true;
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public boolean logout(String username) {
		System.out.println("DBAccessor logout: " + username);
		String query = "UPDATE `collectomania1.0`.`tblusers` SET `isLogged`=0 WHERE `username`='"
				+ username + "'";
		Statement statement;
		try {
			statement = this.conn.createStatement();
			if (statement.executeUpdate(query) != 0)
				return true;
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * 
	 * @param pincode
	 *            - decoded string of user
	 * @return - Array list with cid numbers of cards. If pincode is illegal,
	 *         returns null.
	 */
	public ArrayList<CardTemplate> getCardsOfPincode(String pincode) {
		String query = "select * from `collectomania1.0`.`tblcodecards` "
				+ "JOIN `collectomania1.0`.`tblPincode` "
				+ "ON `tblcodecards`.`code` = `tblPincode`.`code` "
				+ "WHERE ISNULL(`useDate`) "
				+ "AND `tblPincode`.`code` =?";
		PreparedStatement  statement;
		ArrayList<CardTemplate> ans = new ArrayList<CardTemplate>();
		try {
			statement = this.conn.prepareStatement(query);
			statement.setString(1, pincode);
			ResultSet rs = statement.executeQuery();
			// gets all cards
			while (rs.next()) {
				int s = rs.getInt("cardId");
				ans.add(getCardTemplateById(s));
			}

			// marks codes as used
			String query2 = "UPDATE `collectomania1.0`.`tblPincode` SET `useDate`=sysdate() WHERE `code`=?";
			try {
				PreparedStatement statement2 = this.conn.prepareStatement(query2);
				statement2.setString(1, pincode);
				boolean res = statement2.execute();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		if (ans.size() <= 0)
			return ans;
		return ans;
	}

	/**
	 * This will add the new cards to the usercards table
	 * 
	 * @param pincode
	 *            - decoded string of user
	 * @param username
	 *            - The name of the user
	 * @return - Array list with iid numbers of cards. If pincode is illegal,
	 *         returns null.
	 */
	public ArrayList<CardInstance> getCardsOfPincode(String pincode, String username) {
		ArrayList<CardTemplate> templates = getCardsOfPincode(pincode);
		ArrayList<CardInstance> ans = null;
		String query = "select MAX(`instanceId`) m from `collectomania1.0`.`tblusercards`";
		Statement statement;
		int max = 0;

		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			if (rs.next()) {
				max = rs.getInt("m");
			}

			if (max > 0) {
				ans = new ArrayList<CardInstance>();
				for (int i = 0; i < templates.size(); ++i) {
					max++;
					query = "INSERT INTO `collectomania1.0`.`tblusercards`  VALUES ("
							+ max
							+ ",'"
							+ username
							+ "',"
							+ templates.get(i).templateID
							+ ",NULL)";
					statement.execute(query);
					ans.add(getCardByIid(max));
				}
				
				//remove cards from wishlist
				for(int i=0;i<ans.size();++i){
					CardInstance card = ans.get(i);
					query = "DELETE FROM `collectomania1.0`.`tblwishlist` " +
							"WHERE `cardId`="+card.templateID+" " +
							"AND `userId`='"+username+"'";
					statement.execute(query);
				}
			}
			return ans;

		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList<CardInstance>();
		}
	}

	/**
	 * Meanwhile has a STUB implementation
	 * 
	 * @param iid
	 *            - iid of card instance
	 * @return object of cardInstance built according field in DB
	 */
	public CardInstance getCardByIid(int iid) {
		String query = "select `tblusercards`.`cardId`, "
				+ "`tblcards`.`pictureURL`, " 
				+ "`tblcards`.`description`, "
				+ "`tblcards`.`albumId`, "
				+ "'multimedia', " 
				+ "`tblcards`.`valueOfCard`, "
				+ "`tblusercards`.`instanceId` "
				+ "from `collectomania1.0`.`tblusercards` "
				+ "JOIN `collectomania1.0`.`tblcards` "
				+ "ON `tblusercards`.`cardId` = `tblcards`.`Id` "
				+ "AND `tblusercards`.`instanceId` = " + iid;
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			if (rs.next()) {
				int template = rs.getInt("cardId");
				String url = rs.getString("pictureURL");
				String description = rs.getString("description");
				String multimedia = rs.getString("multimedia");
				int valueOfCard = rs.getInt("valueOfCard");
				int albumId = rs.getInt("albumId");

				return new CardInstance(template, url, description, multimedia,
						valueOfCard, iid,albumId);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 
	 * @param cid
	 * @return card template with card ID cid
	 */
	public CardTemplate getCardTemplateById(int cid) {
		CardTemplate ans = null;
		String query = "SELECT `id`,`albumId`,`pictureURL`,`description`,'Multimedia',`valueOfCard` "
				+ " from `collectomania1.0`.`tblcards` where `id`=" + cid;

		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			if (rs.next()) {
				int id = rs.getInt("id");
				String url = rs.getString("pictureURL");
				String name = rs.getString("description");
				String multi = rs.getString("Multimedia");
				int val = rs.getInt("valueOfCard");
				int albumId = rs.getInt("albumId");
				ans = new CardTemplate(id, url, name, multi, val,albumId);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ans;
	}

	/**
	 * 
	 * @param username
	 * @return the user with all its data
	 */
	public User getUserByName(String username) {

		if (!isRegistered(username))
			return null;

		ArrayList<CardInstance> userCrads = new ArrayList<CardInstance>();
		userCrads = getUserCards(username);
		ArrayList<CardInstance> tradeList = new ArrayList<CardInstance>();
		tradeList = getUserTradeList(username);
		ArrayList<CardTemplate> wishList = new ArrayList<CardTemplate>();
		wishList = getUserWishList(username);
		ArrayList<AlbumInstance> userAlbums = new ArrayList<AlbumInstance>();
		userAlbums = getUserAlbums(username);

		return new User(username, userAlbums, userCrads, wishList, tradeList);
	}

	/**
	 * 
	 * @param username
	 * @return returns list of users album templates
	 */
	private ArrayList<AlbumInstance> getUserAlbums(String username) {

		ArrayList<AlbumInstance> ans = new ArrayList<AlbumInstance>();
		ArrayList<Integer> albumIds = getUserAlbumsIds(username);

		for (int i = 0; i < albumIds.size(); ++i) {
			ans.add(getAlbumInstance(albumIds.get(i), username));
		}

		return ans;
	}

	/**
	 * 
	 * @param username
	 * @return list of users albums Ids
	 */
	private ArrayList<Integer> getUserAlbumsIds(String username) {
		ArrayList<Integer> ans = new ArrayList<Integer>();

		String query = "SELECT `albumId` FROM `collectomania1.0`.`tbluseralbums` WHERE `userName`='"
				+ username + "'";
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			while (rs.next()) {
				ans.add(rs.getInt("albumId"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ans;
	}

	/**
	 * 
	 * @param templateId
	 * @param username
	 * @return returns album instance by user name and template id
	 */
	public AlbumInstance getAlbumInstance(int templateId, String username) {
		AlbumInstance ans = null;
		String query = "SELECT * FROM `collectomania1.0`.`tbluseralbums` WHERE `userName`='"
				+ username + "' AND `albumId`=" + templateId;
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			if (rs.next()) {
				AlbumTemplate template = getAlbumTemplateById(templateId);
				ArrayList<Integer> pasted = getPastedCards(templateId, username);
				ans = new AlbumInstance(template, username, pasted);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ans;
	}

	/**
	 * 
	 * @param templateId
	 * @param username
	 * @return List of instance ids of pasted cards of users album
	 */
	private ArrayList<Integer> getPastedCards(int templateId, String username) {
		ArrayList<Integer> ans = new ArrayList<Integer>();
		String query = "SELECT U.`instanceId` "
				+ "FROM `collectomania1.0`.`tblusercards` U "
				+ "JOIN `collectomania1.0`.`tblcards` C "
				+ "ON U.`cardId` = C.`id` " + "WHERE C.`albumId`=" + templateId
				+ " " + "AND U.`userName`='" + username + "' "
				+ "AND NOT ISNULL(U.`pastedAt`)";
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			while (rs.next()) {
				ans.add(rs.getInt("instanceId"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ans;
	}

	public AlbumTemplate getAlbumTemplateById(int id) {
		AlbumTemplate ans = null;
		String query = "select * from `collectomania1.0`.`tblalbums` where `id`="
				+ id;

		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			if (rs.next()) {
				String name = rs.getString("albumName");
				String url = rs.getString("coverURL");
				ArrayList<CardTemplate> cards = getAlbumCards(id);
				ans = new AlbumTemplate(id, name, url, cards);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ans;
	}

	private ArrayList<CardTemplate> getAlbumCards(int id) {
		ArrayList<CardTemplate> ans = new ArrayList<CardTemplate>();
		ArrayList<Integer> cardIds = new ArrayList<Integer>();

		String query = "select `id` from `collectomania1.0`.`tblcards` where `albumId`="
				+ id;

		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			while (rs.next()) {
				cardIds.add(rs.getInt("id"));
			}

			for (int i = 0; i < cardIds.size(); ++i) {
				ans.add(getCardTemplateById(cardIds.get(i)));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ans;
	}

	/**
	 * @param username
	 * @return the user wish list (CardTemplate)
	 */
	public ArrayList<CardTemplate> getUserWishList(String username) {

		ArrayList<CardTemplate> ans = new ArrayList<CardTemplate>();

		String query = "select `cardId` from `collectomania1.0`.`tblwishlist` WHERE `userId` = '"
				+ username + "'";
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			while (rs.next()) {
				int id = rs.getInt("cardId");
				CardTemplate ci = getCardTemplateById(id);
				if (ci != null)
					ans.add(ci);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ans;
	}

	/**
	 * @param username
	 * @return return the trade list of the user. (card instances)
	 */
	public ArrayList<CardInstance> getUserTradeList(String username) {
		ArrayList<CardInstance> ans = new ArrayList<CardInstance>();

		String query = "SELECT `cardId` FROM `collectomania1.0`.`tbltradelist` "+
						"WHERE userId='"+username+"'";
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			while (rs.next()) {
				int id = rs.getInt("cardId");
				int iid = getFirstFreeCard(username, id);
				if(iid != -1){
					CardInstance ci = getCardByIid(iid);
					if (ci != null)
						ans.add(ci);
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ans;
	}

	/**
	 * 
	 * @param card
	 *            - card intance
	 * @param username
	 *            - username
	 */
	public boolean addToTradeList(int cardID, String username) {
		System.out.println("DBACcessor addToTradeList");
		String query = "Select * from `collectomania1.0`.`tbltradelist` Where `cardId`="
				+ cardID + " AND `userId`='" + username + "'";
		System.out.println("The first query is " + query);
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			if (!rs.next()) {
				query = "SELECT * FROM `collectomania1.0`.`tblusercards` "
						+ "WHERE `userName` = '" + username + "' "
						+ "AND `cardId` = " + cardID + " "
						+ "AND ISNULL(`pastedAt`)";
				System.out.println("The second query is " + query);
				rs = statement.executeQuery(query);
				// checks that user has free card
				if (rs.next()) {
					query = "insert into `collectomania1.0`.`tbltradelist` values("
							+ cardID + ",'" + username + "')";
					System.out.println("The third query is " + query);
					statement.executeUpdate(query);
				} else {
					return false; // card is pasted
				}
			} else
				return false; // already on list

		} catch (SQLException e) {
			System.out.println("Exception cought " + e);
			e.printStackTrace();
			return false; // some error
		}
		return true;
	}

	public boolean removeFromTradeList(int cardID, String username) {
		System.out.println("DBACcessor removeFromTradeList " + username + " "
				+ cardID);

		String query = "Select * from `collectomania1.0`.`tbltradelist` Where `cardId`="
				+ cardID + " AND `userId`='" + username + "'";
		System.out.println("The select query is : " + query);
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			if (rs.next()) {
				query = "delete from `collectomania1.0`.`tbltradelist` Where `cardId`="
						+ cardID + " AND `userId`='" + username + "'";
				System.out.println("The delete query is : " + query);
				statement.executeUpdate(query);
			} else {
				System.out.println("The card does not exist!!!");
				return false;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * 
	 * @param card
	 *            - crad template
	 * @param username
	 *            - user name
	 */
	public boolean addToWishList(int cardID, String username) {
		System.out.println("DBAcessor addToWishList "+cardID);
		String query = "Select * from `collectomania1.0`.`tblwishlist` Where `cardId`="
				+ cardID + " AND `userId`='" + username + "'";
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			if (!rs.next()) {
				query = "insert into `collectomania1.0`.`tblwishlist` values("
						+ cardID + ",'" + username + "')";
				statement.executeUpdate(query);
				System.out.println(cardID+" was added to wishlist of "+username);
			} else
				return false;

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public boolean removeFromWishList(int cardID, String username) {
		System.out.println("DBACcessor removeFromWishList");
		String query = "Select * from `collectomania1.0`.`tblwishlist` Where `cardId`="
				+ cardID + " AND `userId`='" + username + "'";
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			if (rs.next()) {
				query = "delete from `collectomania1.0`.`tblwishlist` Where `cardId`="
						+ cardID + " AND `userId`='" + username + "'";
				statement.executeUpdate(query);
			} else
				return false;

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * 
	 * @param username
	 * @return All card instances of the user
	 */
	public ArrayList<CardInstance> getUserCards(String username) {

		ArrayList<CardInstance> ans = new ArrayList<CardInstance>();

		String query = "select `instanceId` from `collectomania1.0`.`tblusercards` WHERE `userName` = '"
				+ username + "'";
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			while (rs.next()) {
				int id = rs.getInt("instanceId");
				CardInstance ci = getCardByIid(id);
				if (ci != null)
					ans.add(ci);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ans;
	}

	public ArrayList<User> findMatchingUsers(String username) {
		ArrayList<User> ans = new ArrayList<User>();

		String query = "SELECT a.`userId`,a.`CNT`+ b.`CNT` CNT " + "FROM "
				+ "( " + "SELECT T.`userId` , COUNT(*) CNT "
				+ "FROM `collectomania1.0`.`tblwishlist` W "
				+ "JOIN `collectomania1.0`.`tbltradelist` T "
				+ "ON W.`cardId` = T.`cardId` " + "WHERE W.`userId`='"
				+ username + "' " + "GROUP BY T.`userId` HAVING CNT > 0" + ") a " + "JOIN "
				+ "( " + "SELECT W.`userId` , COUNT(*) CNT "
				+ "FROM `collectomania1.0`.`tblwishlist` W "
				+ "JOIN `collectomania1.0`.`tbltradelist` T "
				+ "ON W.`cardId` = T.`cardId` " + "WHERE T.`userId`='"
				+ username + "' " + "GROUP BY W.`userId`  HAVING CNT > 0" + ") b "
				+ "ON a.`userId` = b.`userId` " + "WHERE a.`userId` <> '"
				+ username + "' " + "ORDER BY CNT DESC";
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			while (rs.next()) {
				String id = rs.getString("userId");
				ans.add(getUserByName(id));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ans;
	}

	public ArrayList<Integer> pasteCard(String username, int cardInstanceID,
			int albumID) {
		ArrayList<Integer> ans = new ArrayList<Integer>();

		String query = "UPDATE `collectomania1.0`.`tblusercards` "
				+ "SET `pastedAt`=`cardId` " + "WHERE `instanceId` = "
				+ cardInstanceID + " " + "AND `userName`='" + username + "'";

		Statement statement;
		try {
			statement = this.conn.createStatement();
			statement.executeUpdate(query);

			query = "SELECT DISTINCT U.`instanceId` "
					+ "FROM `collectomania1.0`.`tblusercards` U "
					+ "JOIN `collectomania1.0`.`tblcards` C "
					+ "ON U.`cardId` = C.`id` "
					+ "WHERE NOT isnull(U.`pastedAt`) " + "AND C.`albumId`="
					+ albumID + " " + "AND U.`username`='" + username + "'";

			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			while (rs.next()) {
				ans.add(rs.getInt("instanceId"));
			}

			removeFromTradeAfterPaste(username, cardInstanceID);

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ans;
	}

	private void removeFromTradeAfterPaste(String username, int cardInstanceID) {

		CardInstance ci = getCardByIid(cardInstanceID);
		int cardTemplateId = ci.templateID;
		if (!checkUserHasCard(username, cardTemplateId)) {
			removeFromTradeList(cardTemplateId, username);
		}
	}

	public String getNotifications(String username) {
		String ans = "";

		String query = "SELECT DISTINCT `content` "
				+ "FROM `collectomania1.0`.`tblnotifications` N "
				+ "JOIN `collectomania1.0`.`tbluseralbums` A "
				+ "ON A.`albumId` = N.`albumId` " + "WHERE A.`userName`='"
				+ username + "' " + "UNION " + "SELECT `content` "
				+ "FROM `collectomania1.0`.`tblnotifications` "
				+ "WHERE ISNULL(`albumId`)";
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			while (rs.next()) {
				ans += rs.getString("content") + " --- ";
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ans;

	}

	public ArrayList<AlbumTemplate> getActiveAlbums() {
		ArrayList<AlbumTemplate> ans = new ArrayList<AlbumTemplate>();

		String query = "SELECT `id` FROM `collectomania1.0`.`tblalbums` WHERE `isActive`=1";
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			// gets all cards
			while (rs.next()) {
				ans.add(getAlbumTemplateById(rs.getInt("id")));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return ans;
	}

	public boolean createTradeInvite(String requestingUser,
			String acceptingUser, int givesID, int getsID) {

		String query = "INSERT INTO `collectomania1.0`.`tblinvites` VALUES "
				+ "('"
				+ "TRADE" 
				+ "','"
				+ requestingUser
				+ "','"
				+ acceptingUser
				+ "',"
				+ givesID
				+ ","
				+ getsID + ",0)";

		Statement statement;
		try {
			statement = this.conn.createStatement();
			statement.executeUpdate(query);
			// gets all cards

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	// public boolean acceptTradeInvite(String requestingUser,
	// String acceptingUser, int givesID, int getsID) {
	//
	// if (!checkInvite(requestingUser, acceptingUser, givesID, getsID))
	// return false;
	// if (!checkUserHasCard(requestingUser, givesID))
	// return false;
	// if (!checkUserHasCard(acceptingUser, getsID))
	// return false;
	// if (!removeFromInvites(requestingUser, acceptingUser, givesID, getsID))
	// return false;
	//
	// int givesInstance = getFirstFreeCard(requestingUser, givesID);
	// int getInstance = getFirstFreeCard(acceptingUser, getsID);
	//
	// if (givesInstance == -1 || getInstance == -1)
	// return false;
	// boolean giveNewUser = setNewUser(givesInstance, acceptingUser);
	// if (giveNewUser) {
	// return setNewUser(getInstance, requestingUser);
	// }
	//
	// return false;
	// }

	private boolean setNewUser(int iid, String username) {

		String query = "UPDATE `collectomania1.0`.`tblusercards` "
				+ "SET `userName`='" + username + "' "
				+ "WHERE `instanceId` = " + iid;

		Statement statement;
		try {
			statement = this.conn.createStatement();
			statement.executeUpdate(query);
			return true;
			// gets all cards

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	private int getFirstFreeCard(String username, int cid) {
		String query = "SELECT `instanceId` FROM `collectomania1.0`.`tblusercards` "
				+ "WHERE `userName`='"
				+ username
				+ "' "
				+ "AND `cardId`="
				+ cid + " " + "AND ISNULL(`pastedAt`) " + "LIMIT 0,1 ";
		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			if (rs.next()) {
				return rs.getInt("instanceId");
			} else {
				return -1;
			}
			// gets all cards

		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}

	private boolean removeFromInvites(String requestingUser,
			String acceptingUser, int givesID, int getsID) {
		String query = "DELETE FROM `collectomania1.0`.`tblinvitesgame` "
				+ "WHERE `gameId`=-1 " + "AND `challenger`='" + requestingUser
				+ "' " + "AND `reciever`='" + acceptingUser + "' "
				+ "AND `cardId`=" + givesID + " " + "AND `cardId2`=" + getsID;

		Statement statement;
		try {
			statement = this.conn.createStatement();
			statement.executeUpdate(query);
			return true;
			// gets all cards

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	private boolean checkUserHasCard(String username, int cid) {

		String query = "SELECT * FROM `collectomania1.0`.`tblusercards` "
				+ "WHERE `userName`='" + username + "' " + "AND `cardId` = "
				+ cid + " " + "AND ISNULL(`pastedAt`) ";

		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			return rs.next();
			// gets all cards

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	private boolean checkInvite(String requestingUser, String acceptingUser,
			int givesID, int getsID) {

		String query = "SELECT * FROM `collectomania1.0`.`tblinvitesgame` "
				+ "WHERE `gameId`=-1 " + "AND `challenger`='" + requestingUser
				+ "' " + "AND `reciever`='" + acceptingUser + "' "
				+ "AND `cardId`=" + givesID + " " + "AND `cardId2`=" + getsID;

		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			return rs.next();
			// gets all cards

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * 
	 * @param username
	 *            - who receives an invites
	 * @return - list of invites for user with username. If there is no invites
	 *         return an empty list but not null.
	 */
	public ArrayList<Invite> getInvites(String username) {
		ArrayList<Invite> ans = new ArrayList<Invite>();

		String query = "SELECT * FROM `collectomania1.0`.`tblInvites` "
				+ "WHERE `reciever`='" + username + "'";

		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			while (rs.next()) {
				String type = rs.getString("inviteType");
				String challenge = rs.getString("challenger");
				int gives = rs.getInt("cardId");
				int wants = rs.getInt("cardId2");

				Invite invite = getInvite(challenge, username, wants, gives,
						type);
				if (invite != null)
					ans.add(invite);
			}
			// gets all cards

		} catch (SQLException e) {
			e.printStackTrace();
			return ans;
		}

		return ans;
	}

	/**
	 * update invite status to 1 (CONFIRMED). If there is no such request in DB
	 * (probably askingUser canceled game request) should return null.
	 * 
	 * @return in case of error return null
	 */
	public Invite confirmInvite(String askUsername, String recievingUsername,
			int wantInstanceID, int giveInstanceID, String type) {
		
		CardInstance want = getCardByIid(wantInstanceID);
		CardInstance give = getCardByIid(giveInstanceID);
		
		if(want == null || give == null) return null;
		
		int wantTemplateID = want.templateID;
		int giveTemplateID = give.templateID;
		
		Invite invite = getInvite(askUsername, recievingUsername, giveTemplateID,wantTemplateID, type);
		
		if(invite != null){
			String query = "UPDATE `collectomania1.0`.`tblInvites` "+
					"SET `status` = 1 " +
					"WHERE `challenger`='"+askUsername+"' " +
					"AND `reciever`='"+recievingUsername+"' " +
					"AND `cardId`="+giveTemplateID+" "+
					"AND `cardId2`="+wantTemplateID+" " +
					"AND `inviteType`='"+type+"'";
			//System.out.println(query);
			Invite tmpAns = getInvite(askUsername, recievingUsername, giveTemplateID,wantTemplateID, type);
			try {
				Statement statement = this.conn.createStatement();
				statement.executeUpdate(query);
				
				if(type.equals("TRADE")){
					query = "UPDATE `collectomania1.0`.`tblusercards` "+
							"SET `userName`='"+askUsername+"' "+
							"WHERE `instanceId`="+ give.instanceID;
					statement.executeUpdate(query);
					query = "UPDATE `collectomania1.0`.`tblusercards` "+
							"SET `userName`='"+recievingUsername+"' "+
							"WHERE `instanceId`="+ want.instanceID;
					
					statement.executeUpdate(query);
					
					//I hate this shit!!!!!!!!!!!!!!!!11 one eleven
					removeFromTradeList(give.templateID, recievingUsername);
					removeFromWishList(want.templateID, recievingUsername);
					
					removeFromWishList(give.templateID, askUsername);
					removeFromTradeList(want.templateID, askUsername);
					
					
				}
				return tmpAns;
				// gets all cards

			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
		}
		return null;

	}

	private Invite getInvite(String askUsername, String recievingUsername,
			int wanttemplateID, int givetemplateID, String type) {

		String query = "SELECT * FROM `collectomania1.0`.`tblInvites` "
				+ "WHERE `challenger`='" + askUsername + "' "
				+ "AND `reciever`='" + recievingUsername + "' "
				+ "AND `cardId`=" + givetemplateID + " " + "AND `cardId2`="
				+ wanttemplateID + " " + "AND `inviteType`='" + type + "' "
				+ "AND `status` = 0";

		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			if (rs.next()) {
				int status = rs.getInt("status");
				CardInstance want = getCardByIid(getFirstFreeCard(askUsername,
						givetemplateID));
				CardInstance give = getCardByIid(getFirstFreeCard(
						recievingUsername, wanttemplateID));
				if (want != null && give != null)
					return new Invite(askUsername, recievingUsername, give,
							want, type, status);
				else
					return null;
			}
			// gets all cards

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		return null;
	}

	private Invite getInvite(String askUsername, String recievingUsername,
			CardInstance want, CardInstance give, String type) {

		String query = "SELECT * FROM `collectomania1.0`.`tblInvites` "
				+ "WHERE `challenger`='" + askUsername + "' "
				+ "AND `reciever`='" + recievingUsername + "' "
				+ "AND `cardId`=" + give.templateID + " " + "AND `cardId2`="
				+ want.templateID + " " + "AND `inviteType`='" + type + "'";

		Statement statement;
		try {
			statement = this.conn.createStatement();
			ResultSet rs = statement.executeQuery(query);
			if (rs.next()) {
				int status = rs.getInt("status");
				// CardInstance want =
				// getCardByIid(getFirstFreeCard(askUsername,giveInstanceID));
				// CardInstance give =
				// getCardByIid(getFirstFreeCard(recievingUsername,wantInstanceID));
				if (want != null && give != null)
					return new Invite(askUsername, recievingUsername, give,
							want, type, status);
				else
					return null;
			}
			// gets all cards

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

		return null;
	}

	/**
	 * 
	 * Update invite status to -1 (DECLINED)
	 * 
	 * @return
	 */
	public boolean declineInvite(String askUsername, String recievingUsername,
			int wantInstanceID, int giveInstanceID, String type) {

		CardInstance want = getCardByIid(wantInstanceID);
		CardInstance give = getCardByIid(giveInstanceID);
		if (want == null || give == null)
			return false;

		int wantTemplateID = want.templateID;
		int giveTemplateID = give.templateID;
		Invite invite = getInvite(askUsername, recievingUsername,
				giveTemplateID, wantTemplateID, type);

		if (invite != null) {
			String query = "UPDATE `collectomania1.0`.`tblInvites` "
					+ "SET `status` = -1 " + "WHERE `challenger`='"
					+ askUsername + "' " + "AND `reciever`='"
					+ recievingUsername + "' " + "AND `cardId`="
					+ wantTemplateID + " " + "AND `cardId2`=" + giveTemplateID
					+ " " + "AND `inviteType`='" + type + "'";
			try {
				Statement statement = this.conn.createStatement();
				statement.executeUpdate(query);
				return true;
				// gets all cards

			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return false;
	}

	/**
	 * 
	 * @param instanceID
	 * @return name of card with instanceID
	 */
	public String getCardName(int instanceID) {
		CardInstance card = getCardByIid(instanceID);
		if (card != null)
			return card.name;

		return "";
	}

	/**
	 * Creates a game invite when type is game name.<br>
	 * If invite is already sent, or there was some other problem returns null.
	 * 
	 * @param requestingUser
	 *            - username
	 * @param acceptingUser
	 *            - username
	 * @param givesID
	 *            - instanceID
	 * @param getsID
	 *            - instanceID
	 * @param type
	 * @return false if such invite was already sent
	 */
	public Invite createGameInvite(String requestingUser, String acceptingUser,
			int givesID, int getsID, String type) {
		CardInstance wantCard = getCardByIid(getFirstFreeCard(acceptingUser,
				getsID));
		CardInstance giveCard = getCardByIid(getFirstFreeCard(requestingUser,
				givesID));
		if (wantCard != null && giveCard != null) {
			if (!checkUserHasCard(requestingUser, giveCard.templateID))
				return null;
			if (!checkUserHasCard(acceptingUser, wantCard.templateID))
				return null;

			String query = "INSERT INTO `collectomania1.0`.`tblinvites` VALUES "
					+ "('"
					+ type
					+ "','"
					+ requestingUser
					+ "','"
					+ acceptingUser
					+ "',"
					+ giveCard.templateID
					+ ","
					+ wantCard.templateID + ",0)";

			Statement statement;
			try {
				statement = this.conn.createStatement();
				statement.executeUpdate(query);
				return getInvite(requestingUser, acceptingUser, wantCard,
						giveCard, type);
				// gets all cards

			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
		}

		return null;
	}

	/**
	 * Remove game request from invites table
	 * 
	 * @param askingUsername
	 * @param receivingUsername
	 * @param giveCardID
	 * @param wantCardID
	 * @param type
	 * @return false in case of error
	 */
	public boolean cancelGameInvite(String askUsername,
			String recievingUsername, int giveInstanceID, int wantInstaceID,
			String type) {

		CardInstance want = getCardByIid(wantInstaceID);
		CardInstance give = getCardByIid(giveInstanceID);

		if (want == null || give == null)
			return false;
		int wantTemplateID = want.templateID;
		int giveTemplateID = give.templateID;

		Invite invite = getInvite(askUsername, recievingUsername,
				wantTemplateID, giveTemplateID, type);

		if (invite != null) {
			String query = "DELETE FROM `collectomania1.0`.`tblInvites` "
					+ "WHERE `challenger`='" + askUsername + "' "
					+ "AND `reciever`='" + recievingUsername + "' "
					+ "AND `cardId`=" + giveTemplateID + " " + "AND `cardId2`="
					+ wantTemplateID + " " + "AND `inviteType`='" + type + "'";
			try {
				Statement statement = this.conn.createStatement();
				statement.executeUpdate(query);
				return true;
				// gets all cards

			} catch (SQLException e) {
				e.printStackTrace();
				return false;
			}
		}
		return false;
	}

	/**
	 * Called when the game is finished and there is no tie.
	 * 
	 * @param winner
	 * @param looser
	 * @param winnerCardID
	 * @param looserCardID
	 * @return false in case of error
	 */
	public boolean fromLooserToWinner(String winner, String looser,
			int looserCardID, int winnerCardID) {
		
		String query = "UPDATE `collectomania1.0`.`tblusercards` "+
						"SET `userName`='"+winner+"' "+
						"WHERE `instanceId`="+looserCardID;	
		
		try {
			Statement statement = this.conn.createStatement();
		    statement.executeUpdate(query);
		    
		    CardInstance card = getCardByIid(looserCardID);
		    
		    if(card == null) return false;
			return removeFromTradeList(card.templateID, looser);
			// gets all cards
	
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Called when game is finished. Remove game row from invites table.
	 * 
	 * @param user1
	 * @param user2
	 * @param cardID1
	 * @param cardID2
	 * @return false in case of any error
	 */
	public boolean removeGameInvite(String user1, String user2, int cardID1,
			int cardID2) {
		
		CardInstance one = getCardByIid(cardID1);
		CardInstance two = getCardByIid(cardID2);
		
		if(one == null || two == null) return false;
		
		String query = "DELETE FROM `collectomania1.0`.`tblinvites` "+
						"WHERE `challenger`='"+user1+"' "+
						"AND `reciever`='"+user2+"' "+
						"AND `cardId` = "+two.templateID+" "+
						"AND `cardId2`="+one.templateID;

		try {
			Statement statement = this.conn.createStatement();
			statement.executeUpdate(query);
			return true;
		// gets all cards
		
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
			}
}
